Thursday, January 31, 2019

Use ClosedXML to make sense of an .xlsx extension Excel sheet in a stream of bytes slurped from a file control in C#.

In the app I am working on I just use a Razor form like the one here in a .NET Core MVC project. I grab ahold of the file a user finds like so at a controller action:

[Authorize(Policy = "AdminOnly")]
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Uploading(IFormFile CsdmeNcoaData)
{
   if (CsdmeNcoaData != null)
   {
      string[] piecesOfFileName = CsdmeNcoaData.FileName.Split('.');
      string extension = piecesOfFileName[piecesOfFileName.Length
            - 1].ToLower().Trim();
      if (extension == "xlsx")
      {
         using (var reader = new StreamReader(CsdmeNcoaData.OpenReadStream()))
         {
            using (var memoryStream = new MemoryStream())
            {
               var buffer = new byte[CsdmeNcoaData.Length];
               var bytesRead = default(int);
               while ((bytesRead = reader.BaseStream.Read(buffer, 0, buffer.Length)) > 0)
               {
                  memoryStream.Write(buffer, 0, bytesRead);
                  ExcelHelper.CdsmeNcoaImport(memoryStream);
               }
            }
         }
         ViewBag.Xlsx = "The file was processed.";
      }
      else
      {
         ViewBag.Xlsx = "The file uploaded did not have the .xlsx extension.";
      }
   }
   else
   {
      ViewBag.Xlsx = "No file was uploaded.";
   }
   return View();
}

 
 

I then hand into static utility the MemoryStream above like so:

using System.IO;
using ClosedXML.Excel;
namespace Foo.Bar.Baz.Qux
{
   public static class ExcelHelper
   {
      public static void CdsmeNcoaImport(MemoryStream memoryStream)
      {
         using (XLWorkbook excelWorkbook = new XLWorkbook(memoryStream))
         {
            foreach (IXLWorksheet worksheet in excelWorkbook.Worksheets)
            {
               foreach (IXLRow row in worksheet.Rows())
               {
                  foreach (IXLCell cell in row.Cells())
                  {
                     string setting = cell.Value.ToString();

 
 

It is important to note that the first blob of code above is in a .NET Core project while the second is in a .NET Framework project. I installed ClosedXML (to the .NET Framework project) from the NuGet command prompt with this command line command:

install-package ClosedXML Foo.Bar

No comments:

Post a Comment